--*-----------------------------------------------------------------------------
--
-- HIKE
--
--*-----------------------------------------------------------------------------
IF NOT EXISTS(
  SELECT 1
    FROM information_schema.tables
   WHERE table_name = 'HIKE'
)
BEGIN
  CREATE TABLE HIKE
  (
    ID INT IDENTITY(1,1) NOT NULL
  , NAME NVARCHAR(200) NOT NULL
  , START_DATE DATE NOT NULL
  , END_DATE DATE NOT NULL
  , ROUTE NVARCHAR(MAX)
  , SHORT_DESCRIPTION NVARCHAR(500) NOT NULL
  , DESCRIPTION NVARCHAR(MAX)
  , DURATION AS DATEDIFF(DAY, START_DATE, END_DATE) + 1
  , CATEGORY_ID INT NOT NULL
  , REGION_ID INT NOT NULL
  , PUBLISHED BIT NOT NULL
  , META NVARCHAR(MAX)
  )
  ALTER TABLE HIKE ADD CONSTRAINT PK_HIKE PRIMARY KEY (ID) 
  ALTER TABLE HIKE ADD CONSTRAINT FK_HIKE_REGION FOREIGN KEY (REGION_ID) REFERENCES REGION(ID) ON DELETE CASCADE
  ALTER TABLE HIKE ADD CONSTRAINT FK_HIKE_CATEGORY FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(ID) ON DELETE CASCADE
  ALTER TABLE HIKE ADD CONSTRAINT DF_HIKE_PUBLISHED DEFAULT (0) FOR PUBLISHED
  CREATE INDEX IDX_HIKE_REGION ON HIKE(REGION_ID)
  CREATE INDEX IDX_HIKE_CATEGORY ON HIKE(CATEGORY_ID)
  CREATE INDEX IDX_HIKE_DATE ON HIKE(START_DATE, END_DATE)
END
GO
--*-----------------------------------------------------------------------------
DECLARE @Year INT = 2008
, @Month INT = 1
, @Day INT = 1
, @Date DATE
, @DateExpression VARCHAR(20)
, @MS INT
WHILE @Year<=2015
BEGIN
  SET @Month = 1
  WHILE @Month<=12
  BEGIN
    SET @Day = 1
    WHILE @Day<=31
    BEGIN
      SET @DateExpression = CAST(@Year AS VARCHAR)+'-' + CAST(@Month AS VARCHAR)+'-'+CAST(@Day AS VARCHAR)
      SET @MS = DATEPART(MS, GETDATE())%6
      IF ISDATE(@DateExpression) = 1
      BEGIN
        SET @MS = DATEPART(MS, GETDATE())%6
        SET @Date = @DateExpression
        INSERT INTO hike
               (
                 start_date
               , end_date
               , name
               , route
               , short_description
               , description
               , category_id
               , region_id
               , published
               )
        SELECT   @Date
               , DATEADD(DAY, @MS, @Date)
               , CAST(@Date AS NVARCHAR) + ' ' + country.name + '. ' + region.name
               , country.name + ' - ' + region.name
               , '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat.</p>'
               , '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum.</p>'
               , category.id
               , region.id
               , 1
          FROM category
         CROSS
          JOIN region
          JOIN country
            ON region.country_id = country.id
      END
    SET @Day = @Day+@MS
    END
    SET @MS = DATEPART(MS, GETDATE())%6
    SET @Month = @Month+@MS
  END
  SET @MS = DATEPART(MS, GETDATE())%2
  SET @Year = @Year+@MS
END
GO
